package com.codeoss.framework.db.table;

import java.util.List;

import org.springframework.stereotype.Service;

import com.codeoss.framework.context.YunContext;
import com.codeoss.framework.data.metadata.Order;
import com.codeoss.framework.data.metadata.Query;
import com.codeoss.framework.data.metadata.Table;
import com.codeoss.framework.util.StringUtils;

@Service("sqlserverTableService")
public class SqlServerTableService extends AbstractTableService {
	public String getPageSQL(YunContext yunContext, Table table, List<String> columnList, Query query) {
		int offset=(query.getPage()-1)*query.getLimit();
		StringBuilder sb=new StringBuilder();
		String select=getSqlServerSelectSQL(yunContext,table,columnList,query);
		String orderBy=getSqlServerOrderBySQL(yunContext,table,query);
		if(StringUtils.isEmpty(orderBy)) {
			orderBy=" id ";
		}
		sb.append("select ").append(select).append(" from (select ");
		sb.append(select).append(" ,row_number() over (order by ").append(orderBy).append(") as rnum from ").append(table.getTableName());
		String where = getWhere(table, query,false,false);
		if(!StringUtils.isEmpty(where)) {
			sb.append(where);
		}
		sb.append(")t");
		sb.append(" where t.rnum>").append(offset).append(" and t.rnum<=").append(offset+query.getLimit()); 
		return sb.toString();
	}
	
	protected String getSqlServerSelectSQL(YunContext yunContext, Table table, List<String> selectColumnList, Query query) {
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < selectColumnList.size(); i++) {
			if (i > 0) {
				sb.append(",");
			}
			sb.append(selectColumnList.get(i));
		}
		return sb.toString();
	}
	
	protected String getSqlServerOrderBySQL(YunContext yunContext, Table table, Query query) {
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < query.getOrderList().size(); i++) {
			Order order=query.getOrderList().get(i);
			
			if(i>0){
				sb.append(",");
			}
			String ordersql=order.getOrderSQL();
			if(!StringUtils.isEmpty(ordersql)){
				sb.append(ordersql);
			}else{
				sb.append(order.getName()).append(" ").append(order.getOrder());
			}
		}
		return sb.toString();
	}
}
